
from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
from PyQt5 import QtWidgets
import datetime,time
import uuid
import sys
import os
from data import *

# import sqlite3
import xlrd


class FileIn(QWidget):

    def __init__(self):
        super(FileIn,self).__init__()

        # self.con = sqlite3.connect('cov19.db')
        self.db = DB()
        self.con = self.db.con
        self.cur = self.db.cur
        self.resize(320,200)
        self.center()
        self.win = None
        self.msg = {'person':0,'contact':0,'sub_contact':0}
        self.id_check_list = []
        self.set_ui()

    def set_ui(self):

        self.table = QComboBox()
        table_list = ['文件夹导入','个案一览表','密接次密接一览表','重点场所一览表','身份证核查']
        for i in table_list:
            self.table.addItem(i)
        self.table.setStyleSheet("border:none;color:green;background:none;height:30px;")
        self.sheet_txt = QComboBox()
        self.sheet_txt.setStyleSheet("border:none;color:green;background:none;height:30px;")

        self.filename = QLineEdit()
        self.filename.setStyleSheet('width:200px;height:30px;border:solid;')
        self.filename.setPlaceholderText("请选择文件/文件夹")
        # self.filename.setReadOnly(True)

        choose_bnt = QPushButton("选择文件/文件夹")
        choose_bnt.clicked.connect(self.choose_file)
        choose_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#00BFFF')
        re_import_bnt = QPushButton('导入数据')
        re_import_bnt.clicked.connect(self.open_file)
        re_import_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#008000')
        reset_data_bnt = QPushButton("重置数据")
        reset_data_bnt.clicked.connect(self.reset_data)
        reset_data_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#313000')
        close_bnt = QPushButton('关闭窗口')
        close_bnt.clicked.connect(lambda:self.close())
        close_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#FF0000')

        self.label = QLabel('')
        self.label.setStyleSheet('width:200px;height:30px;border:solid;color:#008000')
        self.label.setGeometry(QRect(0,0,200,30))
        self.label.setAlignment(Qt.AlignCenter)

        layout = QGridLayout()

        layout.addWidget(self.table,0,0,1,2)
        layout.addWidget(self.sheet_txt,0,2,1,2)
        layout.addWidget(self.filename,1,0,1,4)
        layout.addWidget(choose_bnt,3,0,1,3)
        layout.addWidget(re_import_bnt,3,3,1,1)
        layout.addWidget(reset_data_bnt,4,0,1,1)
        layout.addWidget(close_bnt,4,1,1,3)
        layout.addWidget(self.label,2,0,1,4)

        self.setLayout(layout)


    def choose_file(self):
        if self.table.currentIndex():
            filename,ok = QFileDialog.getOpenFileName(self, '选择数据','./')
            if filename:
                self.filename.setText(filename)
                try:
                    workbook = xlrd.open_workbook(filename)
                    sheets = workbook.sheet_names()
                    if sheets:
                        self.sheet_txt.clear()
                    for i in sheets:
                        self.sheet_txt.addItem(i)
                except:
                    self.sheet_txt.addItem("无sheet")

        else:
            dir  = QFileDialog.getExistingDirectory(self, ('选择数据'),'./',QFileDialog.ShowDirsOnly|QFileDialog.DontResolveSymlinks)
            if dir:
                self.filename.setText(dir)

    def open_file(self):
        parent_path = self.filename.text()
        if os.path.isdir(parent_path):
            for dirpath,dirnames,filenames in os.walk(parent_path):
                for filename in filenames:
                    self.loading()
                    item_path_ = os.path.join(dirpath,filename)
                    if "核心信息" in item_path_:
                        print("core")
                        try:
                            self.import_core(item_path_)
                            self.msg['person'] += 1
                        except:
                            pass
                    if '次密接' in item_path_:
                        print('cimije')
                        if True:
                        # try:
                            self.daoru(item_path_,0,2)
                        # except:
                            # pass
                    if '密接' in item_path_ and ('次密接' not in item_path_):
                        print('mijie')
                        # try:
                        if True:
                            self.daoru(item_path_,0,2,'次密接')
                        # except:
                            # pass
                    if '重点场所一览表' in item_path_ :
                        print('场所')
                        # try:
                        if True:
                            self.daoru(item_path_,0,3)
                        # except:
                            # pass

            tmp = "核心信息{},密接{},次密接{}".format(self.msg['person'],self.msg['contact'],self.msg['sub_contact'])
            if self.id_check_list:
                if self.win:
                    self.win.close()
                self.win = Rslt_Tab(['姓名','身份证','文件','错误'],self.id_check_list)
                self.win.show()
        else:
            sheet_index = self.sheet_txt.currentIndex()
            mark = self.table.currentIndex()
            self.daoru(parent_path,sheet_index,mark)
            tmp = 'abc'

        self.label.setText(tmp)
        self.messagebox_auto_close(tmp)

    def daoru(self,filename,sheet_index,mark,mijie='密接'):

        if filename and mark:
            workbook = xlrd.open_workbook(filename)
            sheet = workbook.sheets()[sheet_index]
            rows = sheet.nrows
            cols = sheet.ncols

            if True:
                self.loading()

                if mark == 1:
                    for i in range(1,rows):

                        # tmp = [sheet.cell(i,k).value for k in range(cols)]
                        tmp = sheet.row_values(i)
                        try:
                            tmp[0] = str(int(tmp[0]))
                        except:
                            tmp[0] = tmp[0]
                        tmp[9] = self.change_date(tmp[9])
                        self.import_person(tmp,filename)
                if mark == 2:
                    for i in range(1,rows):
                        # tmp = [sheet.cell(i,k).value for k in range(cols)]
                        tmp = sheet.row_values(i)
                        tmp[13] = self.change_date(tmp[13])
                        tmp[20] = self.change_date(tmp[20])
                        tmp[3] = tmp[3].upper()
                        tmp[6] = tmp[6].upper()
                        try:
                            tmp[0] = str(int(tmp[0]))
                        except:
                            tmp[0] = tmp[0]
                        today = datetime.datetime.timestamp(datetime.datetime.today())
                        person_tmp = ['',tmp[6],tmp[4],tmp[6],tmp[7],tmp[8],tmp[10],tmp[11],tmp[9],"null",
                                    today,'密接',tmp[19],tmp[21],'hsqcdc',0]

                        if tmp[6]:
                            self.import_person(person_tmp,filename)
                        relation_tmp  = [tmp[6],tmp[3],tmp[13],tmp[14],tmp[15],tmp[12],tmp[5]]
                        self.cur.execute('select * from relation where uuid = "{}" and contact_uuid = "{}"'.format(relation_tmp[0],relation_tmp[1]))
                        dup_rslt = self.cur.fetchall()
                        if relation_tmp[1] and (not dup_rslt):
                            sql = '''insert into relation values (
                                null,"{}","{}",{},"{}","{}","{}","{}"
                            )
                            '''.format(*relation_tmp)
                            print('relation',sql)
                            self.cur.execute(sql)
                        self.cur.execute('select * from xiecha where id = "{}" '.format(relation_tmp[0]))
                        dup_xiecha_rslt = self.cur.fetchall()
                        if (not dup_xiecha_rslt) and (tmp[18] == '是') and tmp[3]:
                            self.cur.execute('insert into xiecha values (null,"{}","{}",{})'.format(relation_tmp[0],mijie,time.time()))

                if mark == 3:
                    for i in range(1,rows):
                        tmp = [sheet.cell(i,k).value for k in range(cols)]
                        tmp[5] = self.change_date(tmp[5])
                        tmp[2] = tmp[2].upper()
                        self.cur.execute('select * from location where uuid = "{}" and address = "{}"'.format(tmp[2],tmp[3]))
                        dup_rslt = self.cur.fetchall()
                        if not dup_rslt:
                            sql = '''insert into location values (
                            "{}","{}","{}","{}","{}",{},"{}","{}","{}"
                            )
                            '''.format(*tmp)
                            print(sql)
                            self.cur.execute(sql)
                try:
                    self.con.commit()
                except:
                    pass
                if mark == 4:
                    rslt_list = []
                    for i in range(1,rows):
                        tmp = [sheet.cell(i,k).value for k in [4,6]]
                        id = tmp[1]
                        rslt = self.id_check(id)
                        if rslt:
                            rslt_list.append(tmp+[filename,rslt])
                    if rslt_list:
                        self.label.setText("done!")
                        if self.win:
                            self.win.close()
                        self.win = Rslt_Tab(['姓名','身份证','文件','错误'],rslt_list)
                        self.win.show()

    def import_core(self,xls):
        workbook = xlrd.open_workbook(xls)
        sheet = workbook.sheets()[0]
        dic = {
            'case_num':(1,2),'present_status':(1,7),'name':(3,3),'gender':(3,7),
            'birthday':(4,3),'occup':(4,5),'tel':(4,7),'id':(5,3),'address':(6,3),
            'company':(7,3),'symptom':(9,3),'symptom_date':(9,7),'sample_date':(10,3),
            'ctn':(10,5),'ctorf':(10,7),'sick_date':(11,3),'diag_date':(11,5),
            'present_measure':(11,7),'hosp_date':(12,3),'heavy_rank':(12,7),
            'is_vacci':(13,3),'dose1_date':(15,2),'dose1_name':(16,2),'dose2_date':(15,3),
            'dose2_name':(16,3),'dose3_date':(15,4),'dose3_name':(16,4),
            'booster_shot1_date':(15,5),'booster_shot1_name':(16,5),'booster_shot2_date':(15,6),
            'booster_shot2_name':(16,6),'booster_shot3_date':(15,7),'booster_shot3_name':(16,7),
            'exposure':(18,7),'patient_name':(19,3),'exposure_date':(19,5),
            'exposure_time':(19,7),'last_contact_date':(20,3),'last_contact':(20,5),
            'other_exposure':(20,7),'detection':(21,5),'backup':(47,1),
            'regist_date':(49,6),
        }
        for k,v in dic.items():
            dic[k] = sheet.cell_value(*v)
        try:
            dic['age'] = int((dic['sick_date'] - dic['birthday'])/365.25)
        except:
            dic['age'] = 0
        dic['register'] = 'hsqcdc'
        date_list = ['birthday','sick_date','diag_date','sample_date','symptom_date','hosp_date',
            'exposure_date','last_contact_date','regist_date','dose1_date','dose2_date',
            'dose3_date','booster_shot1_date','booster_shot2_date','booster_shot3_date']
        for i in date_list:
            dic[i] = self.change_date(dic[i])

        dic['uuid'] = self.gen_uuid()
        print(dic)

        dup_sql = '''select * from person  where id = "{}"
            '''.format(dic['id'])
        self.cur.execute(dup_sql)
        dup_rslt = self.cur.fetchall()
        if not dup_rslt:
            sql = '''insert into person values (
                    "{case_num}","{id}","{name}","{id}","{gender}",{age},"{tel}","{address}",'{occup}',"{company}",
                    {regist_date},"{present_status}","{present_measure}","{backup}","{register}",0
                )'''.format(**dic)
            id_check_rslt = self.id_check(dic['id'])
            if id_check_rslt:
                self.id_check_list.append([dic['name'],dic['id'],xls,id_check_rslt])
            # measure_sql = '''insert into measure values ("{uuid"},"{id}",{regist_date},'{present_measure}',null)
                # '''.format(**dic)
            # status_sql = '''insert into measure values ("{uuid"},"{id}",{regist_date},'{present_status}',null)
                # '''.format(**dic)
        else:
            sql = '''update person set serial = "{case_num}",name = "{name}",id="{id}",
                        gender="{gender}",age={age},tel="{tel}",address="{address}",
                        occup="{occup}",company = "{company}",regist_date={regist_date},
                        present_status="{present_status}",present_measure="{present_measure}",backup="{backup}" where uuid="{id}"
                    '''.format(**dic)

        try:
            vaccine_sql = '''insert into vaccine values ("{id}","{name}",'{is_vacci}',{dose1_date},'{dose1_name}',{dose2_date},
                    '{dose2_name}',{dose3_date},'{dose3_name}',{booster_shot1_date},"{booster_shot1_name}",
                    {booster_shot2_date},"{booster_shot2_name}",{booster_shot3_date},'{booster_shot3_name}')
                    '''.format(**dic)
            print(vaccine_sql)
            self.cur.execute(vaccine_sql)
        except:
            vaccine_sql = '''update vaccine set is_vaccined = '{is_vacci}',dose1_date = {dose1_date},
                    dose1_name = '{dose1_name}',dose2_date = {dose2_date},dose2_name = '{dose2_name}',
                    dose3_date = {dose3_date},dose3_name = '{dose3_name}',booster_shot1_date = {booster_shot1_date},
                    booster_shot1_name = "{booster_shot1_name}",booster_shot2_date = {booster_shot2_date},
                    booster_shot2_name = "{booster_shot2_name}",booster_shot3_date = {booster_shot3_date},
                    booster_shot3_name = '{booster_shot3_name}' where id = "{id}"
                    '''.format(**dic)
            print(vaccine_sql)
            self.cur.execute(vaccine_sql)

            # measure_dup_sql = 'select measure from measure where uuid = "{id}"'.format(dic['id'])
            # measure_dup_tmp = self.cur.fetchall()
            # if measure_dup_tmp:
            #     measure_dup = [i[0] for i in measure_dup_tmp]
            #     if dic['present_measure'] not in measure_dup:
            #         measure_sql = '''insert into measure values ("{uuid"},"{id}",{regist_date},'{present_measure}',null)
            #             '''.format(**dic)
            #     else:
            #         measure_sql = None
            # status_dup_sql = 'select status from status where uuid = "{id}"'.format(dic['id'])
            # status_dup_tmp = self.cur.fetchall()
            # if status_dup_tmp:
            #     status_dup = [i[0] for i in status_dup_tmp]
            #     if dic['present_status'] not in status_dup:
            #         status_sql = '''insert into status values ("{uuid"},"{id}",{regist_date},'{present_status}')
            #             '''.format(**dic)
            #     else:
            #         status_sql = None

        print('core',sql)
        self.cur.execute(sql)
        # if measure_sql:
            # self.cur.execute(measure_sql)
        # if status_sql:
            # self.cur.execute(status_sql)
        try:
            self.con.commit()
        except:
            pass

    def import_person(self,tmp,filename):
        if tmp:
            self.cur.execute('select * from person where id = "{}"'.format(tmp[3]))
            dup_rslt = self.cur.fetchone()
            if not dup_rslt:
                sql = '''insert into person values (
                    "{}","{}","{}","{}","{}",{},"{}","{}","{}","{}",
                    {},"{}","{}","{}",'hsqcdc',0
                )
                '''.format(*tmp)

                id_check_rslt = self.id_check(tmp[3])
                if id_check_rslt:
                    self.id_check_list.append([tmp[2],tmp[3],filename,id_check_rslt])
            else:
                status_list = ['初筛阳性','无症状感染','确诊','康复','疑似病例','临床诊断病例']
                if  tmp[10] in status_list:
                    tmp2 = [tmp[0]] +tmp[2:14] + [tmp[1]]
                    sql = '''update person set serial = "{}",name = "{}",id="{}",
                        gender="{}",age={},tel="{}",address="{}",occup="{}",company="{}",regist_date={},
                        present_status="{}",present_measure="{}",backup="{}" where uuid="{}"
                    '''.format(*tmp2)
                else:
                    tmp2 = tmp[2:11] + [tmp[1]]
                    sql = '''update person set name = "{}",id="{}",
                        gender="{}",age={},tel="{}",address="{}",occup="{}",company="{}",regist_date={}
                        where uuid="{}"
                    '''.format(*tmp2)
            print('contact',sql)
            self.cur.execute(sql)

    def import_contact(self,xls):
        print(xls)

    def import_sub_contact(self,xls):
        print(xls)



    def gen_uuid(self):
        return (str(uuid.uuid1()))

    def reset_data(self):

        self.cur.execute('delete from person')
        self.cur.execute('delete from relation')
        self.cur.execute('delete from travel')
        self.cur.execute('delete from location')
        self.cur.execute('delete from test')
        self.cur.execute('delete from status')
        self.cur.execute('delete from measure')
        self.cur.execute('delete from vaccine')
        self.cur.execute('delete from xiecha')
        msg = QMessageBox.information(self,'tips','是否清空数据库',QMessageBox.Yes,QMessageBox.No)
        if msg == QMessageBox.Yes:
            self.con.commit()


    def change_date(self,time):
        try:
            tmp = (time-70*365-19)*86400 - 8*3600
        except:
            tmp = 1000000
        return tmp

    def loading(self):
        self.gif = QMovie("./img/loading.gif")
        self.label.setMovie(self.gif)
        self.gif.start()

    def id_check(self,id): #身份证号码验证
        id_upper = id.upper()
        if '\'' in id_upper:
            id_upper = id_upper[1:]
        try:
            date_standard = time.strptime(id_upper[6:14],'%Y%m%d')
        except:
            date_standard = 0
        if not date_standard:
            return '出生年月日错误'
        else:
            if len(id_upper) == 18:
                certify_number = ['1','0','X','9','8','7','6','5','4','3','2']
                std_number = [7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2]
                sum = 0
                for i in range(17):
                    sum += int(id_upper[i])*std_number[i]
                certify_rslt = certify_number[sum%11]
                if certify_rslt == id_upper[-1]:
                    return None
                else:
                    return '验证码错误'
            else:
                return '未满18位'


    def center(self):
        self.screen = QDesktopWidget().screenGeometry()
        self.size = self.geometry()
        self.move(int((self.screen.width()-self.size.width())/2),
                 int((self.screen.height()-self.size.height())/2)
                  )

    def messagebox_auto_close(self,txt = '完成!!!',time =1):
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowTitle("# WARNING: ")
        msg.setText(txt)
        msg.setStandardButtons(QMessageBox.Yes)
        msg.button(QMessageBox.Yes).animateClick(time*1000)
        msg.exec_()


class Rslt_Tab(QWidget):

    def __init__(self,head,rslt):
        super(Rslt_Tab,self).__init__()
        self.resize(720,500)
        self.center()
        self.head = head
        self.rslt = rslt
        self.set_ui()

    def set_ui(self):
        cols = len(self.head)
        rows = len(self.rslt)
        table = QTableWidget(rows,cols)
        columns_list = [80,150,300,100]
        for i in range(cols):
            table.setColumnWidth(i,columns_list[i])
        table.setHorizontalHeaderLabels(self.head)
        for i in range(rows):
            for j in range(cols):
                tmp = QTableWidgetItem(str(self.rslt[i][j]))
                table.setItem(i,j,tmp)

        layout = QVBoxLayout()
        bnt = QPushButton('关闭')
        bnt.clicked.connect(lambda:self.close())
        bnt.setStyleSheet('width:150px;height:30px;border:solid;color:white;background:#FF0000')
        layout.addWidget(table)
        layout.addWidget(bnt)
        self.setLayout(layout)

    def center(self):
        self.screen = QDesktopWidget().screenGeometry()
        self.size = self.geometry()
        self.move(int((self.screen.width()-self.size.width())/2),
                 int((self.screen.height()-self.size.height())/2)
                  )
